 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SachOnlineLibrary.DAL
{
    internal class SachDAL
    {
        KetNoiDB ketnoidb;
        internal SachDAL()
        {
            ketnoidb = new KetNoiDB();
        }

        internal DataTable LietKeTatCaSachCSD()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach where TrangThai = 1";
            DataTable dt = new DataTable();
            try
            {
                dt = ketnoidb.ExecuteQuery(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }
        internal DataTable LietKeChiTietSach(string click)
        {
            DataTable dt = new DataTable();
            dt = null;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from sach where " + click + " = masach";
            try
            {
                dt = ketnoidb.ExecuteQuery(cmd);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }
        internal DataTable LietKeTatCaSach()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach";
            return ketnoidb.ExecuteQuery(cmd);
        }
        internal DataTable LietKeTatSachMoi()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 8 * from SACH where TrangThai = 1 order by NgayCapNhat DESC";
            DataTable dt = new DataTable();
            try
            {
                dt = ketnoidb.ExecuteQuery(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }
        internal DataTable LietKeTatSachHot()
        { 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 8 s.* "
                               + "from SACH s, CHITIETPDH c " 
                               + "where s.MaSach = c.MaSach and s.TrangThai = 1 "
                               + "group by s.MaSach , s.MaLoai , s.TenSach , s.MoTa , s.GiaTien , s.SoLuong , s.TacGia ,s.NhaXuatBan, s.NgayCapNhat,s.Hinh,s.TrangThai "
                               + "order by COUNT(s.MaSach) DESC";
            DataTable dt = new DataTable();
            try
            {
                dt = ketnoidb.ExecuteQuery(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }
        internal DataTable LietKeTheoMa(int masach)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach where MaSach = @masach";
            cmd.Parameters.Add("@masach", SqlDbType.Int).Value = masach;
            DataTable dt = new DataTable();
            return dt = ketnoidb.ExecuteQuery(cmd);
        }
        internal DataTable LietKeTheoLoai(int maloai)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from SACH where MaLoai = @maloai";
            cmd.Parameters.Add("@maloai", SqlDbType.Int).Value = maloai;
            DataTable dt = new DataTable();
            return dt = ketnoidb.ExecuteQuery(cmd);
        }
        internal bool ThemSach(int maloai, string tensach, string mota, long giatien, int soluong, string tacgia, string manxb, DateTime ngaycapnhat, string hinh)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "insert into Sach values (@maloai,@manxb,@tensach,@mota,@giatien,@soluong,@tacgia,@ngaycapnhat,@hinh)";
            cmd.Parameters.Add("@maloai", SqlDbType.Int).Value = maloai;
            cmd.Parameters.Add("@manxb", SqlDbType.Int).Value = manxb;
            cmd.Parameters.Add("@tensach", SqlDbType.NVarChar).Value = tensach;
            cmd.Parameters.Add("@mota", SqlDbType.NVarChar).Value = mota;
            cmd.Parameters.Add("@giatien", SqlDbType.BigInt).Value = giatien;
            cmd.Parameters.Add("@soluong", SqlDbType.Int).Value = soluong;
            cmd.Parameters.Add("@tacgia", SqlDbType.NVarChar).Value = tacgia;
            cmd.Parameters.Add("@ngaycapnhat", SqlDbType.SmallDateTime).Value = ngaycapnhat;
            cmd.Parameters.Add("@hinh", SqlDbType.NVarChar).Value = hinh;
            return ketnoidb.ExecuteNonQuery(cmd);
        }

        internal bool SuaSach(int masach, int maloai, string tensach, string mota, long giatien, int soluong, string tacgia, string nhaxuatban, DateTime ngaycapnhat, string hinh)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "update Sach set maloai=@maloai, tensach = @tensach, mota=@mota, giatien = @giatien, soluong= @soluong, tacgia = @tacgia, nhaxuatban = @nhaxuatban, ngaycapnhat=@ngaycapnhat, hinh = @hinh where masach = @masach";
            cmd.Parameters.Add("@masach", SqlDbType.Int).Value = masach;
            cmd.Parameters.Add("@maloai", SqlDbType.Int).Value = maloai;
            cmd.Parameters.Add("@tensach", SqlDbType.NVarChar).Value = tensach;
            cmd.Parameters.Add("@mota", SqlDbType.NVarChar).Value = mota;
            cmd.Parameters.Add("@giatien", SqlDbType.BigInt).Value = giatien;
            cmd.Parameters.Add("@soluong", SqlDbType.Int).Value = soluong;
            cmd.Parameters.Add("@tacgia", SqlDbType.NVarChar).Value = tacgia;
            cmd.Parameters.Add("@nhaxuatban", SqlDbType.NVarChar).Value = nhaxuatban;
            cmd.Parameters.Add("@ngaycapnhat", SqlDbType.SmallDateTime).Value = ngaycapnhat;
            cmd.Parameters.Add("@hinh", SqlDbType.VarChar).Value = hinh;
            return ketnoidb.ExecuteNonQuery(cmd);
        }
        internal bool SuaSach(int masach, int soluong)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "update Sach set soluong= soluong - @soluong where masach = @masach";
            cmd.Parameters.Add("@masach", SqlDbType.Int).Value = masach;
            cmd.Parameters.Add("@soluong", SqlDbType.Int).Value = soluong;
            return ketnoidb.ExecuteNonQuery(cmd);
        }
        internal DataTable LietKeSach(int masach)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach where MaSach=@MaSach";
            cmd.Parameters.AddWithValue("@MaSach", masach);
            return ketnoidb.ExecuteQuery(cmd);
        }
        internal DataTable LietKeSachTheoTrangThai(bool trangthai)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach where trangthai=@trangthai";
            cmd.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
            return ketnoidb.ExecuteQuery(cmd);
        }
        internal DataTable LietKeSachTheoTenVaTacGia(string tensach, string tacgia)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Sach where tensach=@tensach and tacgia=@tacgia";
            cmd.Parameters.Add("@tensach", SqlDbType.NVarChar).Value = tensach;
            cmd.Parameters.Add("@tacgia", SqlDbType.NVarChar).Value = tacgia;
            return ketnoidb.ExecuteQuery(cmd);
        }
    }
}
